package com.dragon.repository.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dragon.repository.config.annotation.DataScope;
import com.dragon.repository.entity.DictsortDetail;
import com.dragon.repository.entity.Loan;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 借款表 Mapper 接口
 * </p>
 *
 * @author sy
 * @since 2023-07-03
 */
@Mapper
public interface LoanMapper extends BaseMapper<Loan> {

    @Select("<script>" +
            "SELECT M.* FROM  " +
            "(SELECT a.*,aa.username AS adminName, ad.name as departmentName, e.name as expensesName FROM dragon_loan a " +
            "left JOIN dragon_admin aa ON a.admin_id = aa.id " +
            "left join dragon_department ad on a.department_id = ad.id " +
            "left join dragon_expenses e on a.loan_type = e.id " +
            "ORDER BY a.create_time DESC) M  " +
            "${ew.customSqlSegment} " +
            "</script>")
    Page<Loan> LoanList(Page<Loan> loanPage, @Param(Constants.WRAPPER) QueryWrapper<Loan> wrapper);

    @Select("SELECT M.* FROM  " +
            "   (SELECT a.*,aa.username as adminName, ad.name as departmentName FROM dragon_loan a " +
            "   LEFT JOIN dragon_admin aa on aa.id = a.admin_id " +
            "   LEFT JOIN dragon_department ad on ad.id = a.department_id ) M " +
            "WHERE M.id = #{id} ")
    Loan selectByLoanId(Integer id);

    /**
     * 获取最大编号
     * @param code
     * @return
     */
    @Select("SELECT MAX(num) FROM dragon_loan WHERE num LIKE CONCAT(#{code},'%')")
    String maxRoundInoutNumber(String code);

    /**
     * 借款 月
     * @return
     */
    @Select("SELECT SUM(a.loan_money) FROM dragon_loan a WHERE a.status = 1 and LEFT( a.create_time,7) = LEFT(NOW(),7)")
    @DataScope(adminAlias = "a", adminField = "admin_id",deptAlias = "a", deptField = "department_id", authCodeString = "loanManagement")
    Double selectMonthLoanMoney();

    /**
     * 借款 总
     * @return
     */
    @Select("SELECT SUM(a.loan_money) FROM dragon_loan a WHERE a.status = 1")
    @DataScope(adminAlias = "a", adminField = "admin_id",deptAlias = "a", deptField = "department_id", authCodeString = "loanManagement")
    Double selectYearLoanMoney();

    /**
     * 借款 年金额
     * @param year
     * @return
     */
    @Select("SELECT SUM(a.loan_money) FROM dragon_loan a WHERE a.status = 1 AND LEFT(a.create_time,4) = #{year} ")
    @DataScope(adminAlias = "a", adminField = "admin_id",deptAlias = "a", deptField = "department_id", authCodeString = "loanManagement")
    Double selectMoneyForYear(String year);


    @Select("with base as(" +
            "    select loan_money money,loan_time from dragon_loan where status = 1" +
            ")," +
            "totalMoney as (" +
            "    select sum(money) totalLoanMoney from base" +
            ")," +
            "yearMoney as (" +
            "    select sum(money) yearLoanMoney from base where left(loan_time,4) = left(now(),4)" +
            ")," +
            "monthMoney as (" +
            "    select sum(money) monthLoanMoney from base where left(loan_time,7) = left(now(),7)" +
            ")," +
            "makeData as (" +
            "    select a.totalLoanMoney,b.yearLoanMoney,c.monthLoanMoney from totalMoney a,yearMoney b,monthMoney c" +
            ")" +
            "select * from makeData")
    Map<String,Object> selectListStatistics();

    /**
     * 借款类型占比
     * @param time
     * @return
     */
    @Select("SELECT ddd.id, " +
            "  ddd.name, " +
            "  IFNULL(SUM(dl.loan_money),0) AS loanTypeMoney, " +
            "  IFNULL( ROUND(SUM(dl.loan_money) / (SELECT SUM(loan_money) FROM dragon_loan where YEAR(create_time) = #{time} ) * 100, 1) ,0)  AS loanTypeAvg " +
            "FROM dragon_dictsort_detail ddd LEFT JOIN dragon_loan dl on ddd.value = dl.loan_type and dl.status = 1 and YEAR(dl.create_time) = #{time}  " +
            "WHERE ddd.code_type = 'expenseCategory' " +
            "GROUP BY ddd.id ")
    List<DictsortDetail> loanTypePercentage(String time);

    /**
     * 借款月总额统计
     * @param time
     * @return
     */
    @Select("SELECT " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '1' THEN dl.loan_money ELSE 0 END ),0) AS '1月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '2' THEN dl.loan_money ELSE 0 END ),0) AS '2月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '3' THEN dl.loan_money ELSE 0 END ),0) AS '3月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '4' THEN dl.loan_money ELSE 0 END ),0) AS '4月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '5' THEN dl.loan_money ELSE 0 END ),0) AS '5月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '6' THEN dl.loan_money ELSE 0 END ),0) AS '6月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '7' THEN dl.loan_money ELSE 0 END ),0) AS '7月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '8' THEN dl.loan_money ELSE 0 END ),0) AS '8月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '9' THEN dl.loan_money ELSE 0 END ),0) AS '9月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '10' THEN dl.loan_money ELSE 0 END ),0) AS '10月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '11' THEN dl.loan_money ELSE 0 END ),0) AS '11月', " +
            "IFNULL(sum( CASE MONTH ( dl.create_time ) WHEN '12' THEN dl.loan_money ELSE 0 END ),0) AS '12月'  " +
            "FROM dragon_loan dl " +
            "WHERE dl.status = 1 AND YEAR ( dl.create_time ) = #{time} ")
    LinkedHashMap<String, Object> loanMonthlyStatistics(String time);
}
